<?php

/**
 * @author @author Samrat Khan & Sadik Sarfaraz - May 14, 2013
 */
require 'foo.config.php';
?>

<?php
if($_REQUEST['action'] == 'Movement') {
    $searchterm = $_GET['st'];

    $query = "
        SELECT 
            t1.serialno AS SISN,
            t1.productid,
            t1.description,
            t1.project_id AS prjID,
            t1.GCIN,
            t1.SUMIN,
            IFNULL(t2.SUMOUT,'0') SUMOUT,
            IFNULL(t2.GCOUT,'0') GCOUT,
            IFNULL(t2.SUMOUT, '0'),
            (t1.SUMIN - IFNULL(t2.SUMOUT, '0')) TOTALHAND,
            IFNULL(t2.OUTQTY, '0'),
            IFNULL(t2.GCOUT, '0')
        FROM (
                SELECT 
                    productid,
                    serialno,
                    description,
                    project_id,
                    SUM(in_quantity) SUMIN, 
                    GROUP_CONCAT(
                        CONCAT(
                            COALESCE(IFNULL(revisionstate,'0'), ''),'|',
                            COALESCE(IFNULL(receiveddate, '0'), ''),'|',
                            COALESCE(IFNULL(shiptype, '0'), ''),'|',
                            COALESCE(IFNULL(siteid, '0'), ''),'|',
                            COALESCE(IFNULL(manufacturingdate, '0'), ''),'|',
                            COALESCE(IFNULL(in_quantity, '0'), ''),'|'
                        )
                    ) GCIN 
                FROM stockin 
                GROUP BY stockin.serialno
            ) t1
            LEFT JOIN
            (
                SELECT 
                IFNULL(productid, '0') productid,
                IFNULL(serialno, '0') serialno,
                IFNULL(out_quantity, '0') OUTQTY,
                SUM(IFNULL(out_quantity,'0')) SUMOUT, 
                GROUP_CONCAT(CONCAT(
                        COALESCE(IFNULL(out_quantity,'0'), ''),'|',
                        COALESCE(IFNULL(eng_id,'0'), ''),'|',
                        COALESCE(IFNULL(requestedby, '0'), ''),'|',
                        COALESCE(IFNULL(siteid, '0'), ''),'|',
                        COALESCE(IFNULL(receivername, '0'), ''),'|',
                        COALESCE(IFNULL(cellnumber, '0'), ''),'|',
                        COALESCE(IFNULL(requesttime, '0'), ''),'|',
                        COALESCE(IFNULL(deliverytime, '0'), ''),'|',
                        COALESCE(IFNULL(employee_id, '0'), ''),'|',
                        COALESCE(IFNULL(transportername, '0'), ''),'|',
                        COALESCE(IFNULL(challannumber, '0'), '')
                    )
                ) GCOUT 
                FROM stockout 
                GROUP BY stockout.serialno
            )  t2
        ON t1.serialno = t2.serialno
        WHERE t1.productid = '".$searchterm."'
        ";
    try {
        $stmt = $db->prepare($query);
        $stmt->execute();
    } catch (PDOException $ex) {
        die("Failed to run query: " . $ex->getMessage());
    }
    $rows = $stmt->fetchAll();
    
    echo "<input class=\"btn btn-primary\" type=\"button\" onclick=\"tableToExcel('movementHistory', 'Movement History')\" value=\"Export to Excel of the following movement history\">";
    echo "<br/>";
    echo "<table id='movementHistory' class='row-fluid table table-striped table-bordered'>";
        echo "<tr>";
            echo "<th>". "&nbsp;" ."</th>";
            echo "<th>". "&nbsp;" ."</th>";
            echo "<th>". "&nbsp;" ."</th>";
            echo "<th>". "&nbsp;" ."</th>";
            echo "<th>". "&nbsp;" ."</th>";
            echo "<th>". "&nbsp;" ."</th>";
            echo "<th>". "&nbsp;" ."</th>";
            echo "<th>". "&nbsp;" ."</th>";
            echo "<th>". "&nbsp;" ."</th>";
            echo "<th>". "Inbound" ."</th>";
            echo "<th>";
                    foreach ($rows as $row1) {
                    $in_qty += $row1['SUMIN'];
                }
                echo $in_qty; 
            echo "</th>";
            echo "<th>";
                foreach ($rows as $row1) {
                    $in_qty1 += $row1['SUMOUT'];
                }
                echo $in_qty1;
            echo "</th>";
            echo "<th>";
                foreach ($rows as $row2) {
                    $in_qty2 += $row2['TOTALHAND'];
                }
                echo $in_qty2;
            echo "</th>";
            echo "<th>". "Outbound" ."</th>";
            echo "<th>". "&nbsp;" ."</th>";
            echo "<th>". "&nbsp;" ."</th>";
            echo "<th>". "&nbsp;" ."</th>";
            echo "<th>". "&nbsp;" ."</th>";
            echo "<th>". "&nbsp;" ."</th>";
            echo "<th>". "&nbsp;" ."</th>";           
            echo "<th>". "&nbsp;" ."</th>";
            echo "<th>". "&nbsp;" ."</th>";
        echo "</tr>";
        echo "<tr>";
            echo "<th>". $stock_in['productid'] ."</th>";
            echo "<th>". $stock_in['serialno'] ."</th>";
            echo "<th>". $stock_in['project_id'] ."</th>";
            echo "<th>". $stock_in['description'] ."</th>";
            echo "<th>". $stock_in['revisionstate'] ."</th>";
            echo "<th>". $stock_in['manufacturingdate'] ."</th>";
            echo "<th>". $stock_in['shiptype'] ."</th>";
            echo "<th>". "SiteID" ."</th>";
            echo "<th>". $stock_in['receiveddate'] ."</th>";
            echo "<th>". $movementhistory['in_qty'] ."</th>";
            echo "<th>". "Sum of In QTY" ."</th>";
            echo "<th>". "Sum of Out QTY" ."</th>";
            echo "<th>". "Total Hand" ."</th>";
            echo "<th>". $movementhistory['out_qty'] ."</th>";
            echo "<th width='100'>". "Engineer Name" ."</th>";
            echo "<th>". "Requested By" ."</th>";
            echo "<th>". $movementhistory['del_site_id'] ."</th>";
            echo "<th>". $movementhistory['del_receiver'] ."</th>";
            echo "<th>". "Request Time" ."</th>";           
            echo "<th>". $movementhistory['del_time'] ."</th>";           
            echo "<th>". $movementhistory['del_employee'] ."</th>";
            echo "<th>". "Transporter Name" ."</th>";
            echo "<th>". "Challan Number" ."</th>";
        echo "</tr>";
        foreach ($rows as $row) {
            echo "<tr>";
                echo "<td>". $row['productid'] ."</td>";
                echo "<td>". changeToSpace($row['SISN']) ."</td>";
                echo "<td>". getName('projects', 'projectname', 'project_id', $row['prjID'], $db) ."</td>";
                echo "<td>". changeToSpace($row['description']) ."</td>";
                echo "<td colspan='6'>";
                    $GCIN = explode(",", $row['GCIN']);
                    $i = 1;
                    foreach($GCIN as $gcin) {
                    echo "<table class='innerTable'><tr>";
                    list($revisionstate, $receiveddate, $shiptype, $siteid, $manufacturingdate, $in_quantity) = explode("|", $gcin);
                    echo "
                        <td>". changeToSpace($revisionstate) ."</td>
                        <td>". changeToSpace($receiveddate) ."</td>
                        <td>". changeToSpace($shiptype) ."</td>
                        <td>". changeToSpace($siteid) ."</td>
                        <td>". changeToSpace($manufacturingdate) ."</td>
                        <td>". changeToSpace($in_quantity) ."</td>
                    ";
                    echo "</tr></table>";
                    $i++;
                    }
                echo "</td>";
                echo "<td>". changeToSpace($row['SUMIN']) ."</td>";
                echo "<td>". changeToSpace($row['SUMOUT']) ."</td>";
                echo "<td>". changeToSpace($row['TOTALHAND']) ."</td>";
                echo "<td colspan='11'>";
                    $GCOUT = explode(",", $row['GCOUT']);
                    $i = 1;
                    foreach($GCOUT as $gcout) {
                        echo "<table class='innerTable'><tr>";
                        list($out_quantity, $eng_id, $requestedby, $siteid, $receivername, $cellnumber, $requesttime, $deliverytime, $employee_id, $transportername, $challannumber) = explode("|", $gcout);
                        echo "
                            <td>". changeToSpace($out_quantity) ."</td>
                            <td>". changeToSpace($eng_id) ."</td>
                            <td>". changeToSpace($requestedby) ."</td>
                            <td>". changeToSpace($siteid) ."</td>
                            <td>". changeToSpace($receivername) ."</td>
                            <td>". changeToSpace($requesttime) ."</td>
                            <td>". changeToSpace($deliverytime) ."</td>
                            <td>" . changeToSpace($employee_id) ."</td>
                            <td>". changeToSpace($transportername) ."</td>
                            <td>". changeToSpace($challannumber) ."</td>
                        ";
                        echo "</tr></table>";
                        $i++;
                    }
                echo "</td>";
            echo "</tr>";
        }
    echo "</table>";
}
if($_REQUEST['action'] == 'StockInHand') {
    $sess_cusID = $_SESSION['user']['cus_id'];
    $pages = new Paginator(' 50', 'page');
    $total = getCountMatchColumn('stockin', 'stockout', 'serialno', 'serialno', 'cus_id', $sess_cusID, $db);
    //$total = getCountMatchColumn('stockin', 'stockin_id', 'cus_id', $sess_cusID, $db);
    $tots = $total[0]['total'];
    $totals = $pages->set_total($tots);
    $limit = $pages->get_limit();
    $rows = selectAllMatchColumn("stockin", "stockout", "serialno", "serialno", $limit, "cus_id", $sess_cusID, $db);
    
    echo "<table class=\"row-fluid table table-striped table-bordered\">";
            echo "<tr>";
                echo "<th>". $stock_in['serialno'] ."</th>";
                echo "<th>". $stock_in['project_id'] ."</th>";
                echo "<th>". $stock_in['productid'] ."</th>";
                echo "<th>". $stock_in['description'] ."</th>";
                echo "<th>". $stock_in['revisionstate'] ."</th>";
                echo "<th>". $stock_in['uom'] ."</th>";
                echo "<th>". $stock_in['in_quantity'] ."</th>";
                echo "<th>". $stock_in['shiptype'] ."</th>";
                echo "<th>". $stock_in['invoicenumber'] ."</th>";
                echo "<th>". $stock_in['ship_no'] ."</th>";
                echo "<th>". $stock_in['mawb'] ."</th>";
                echo "<th>". $stock_in['hawb'] ."</th>";
                echo "<th>". $stock_in['receiveddate'] ."</th>";
                echo "<th>". $stock_in['shipfrom'] ."</th>";
                echo "<th>". $stock_in['boxno'] ."</th>";
                echo "<th>". $stock_in['rma'] ."</th>";
                echo "<th>". $stock_in['warehouse_name'] ."</th>";
                echo "<th>". $stock_in['rackno'] ."</th>";
                echo "<th>". $stock_in['in_docketnumber'] ."</th>";
                echo "<th>". $stock_in['remarks'] ."</th>";
                echo "<th>". $stock_in['userid'] ."</th>";
                echo "<th>". $stock_in['cus_id'] ."</th>";
                echo "<th>". $stock_in['inserteddate'] ."</th>";
            echo "</tr>";
        foreach ($rows as $row) {
            echo "<tr>";
                echo "<td>". $row['serialno'] ."</td>";
                echo "<td>";
                    echo getName('projects', 'projectname', 'project_id', $row['project_id'], $db);
                    echo "<br/>";
                    $idofcustomer = getName('projects', 'cus_id', 'project_id', $row['project_id'], $db);
                    $customerid = getName('customerinfo', 'cus_id', 'cus_id', $idofcustomer, $db);
                echo "</td>";
                echo "<td>". $row['productid'] ."</td>";
                echo "<td>". $row['description'] ."</td>";
                echo "<td>". $row['revisionstate'] ."</td>";
                echo "<td>". getName('uom', 'measurement', 'uom_id', $row['uom'], $db) ."</td>";
                echo "<td>". $row['in_quantity'] ."</td>";
                echo "<td>". getName('shiptype', 'typename', 'shiptype_id', $row['shiptype'], $db) ."</td>";
                echo "<td>". $row['invoicenumber'] ."</td>";
                echo "<td>". $row['shipid'] ."</td>";
                echo "<td>". $row['mawb'] ."</td>";
                echo "<td>". $row['hawb'] ."</td>";
                echo "<td>". $row['receiveddate'] ."</td>";
                echo "<td>". $row['shipfrom'] ."</td>";
                echo "<td>". $row['boxno'] ."</td>";
                echo "<td>". $row['in_rma'] ."</td>";
                echo "<td>". getName('warehouses', 'warehousename', 'warehouse_id', $row['warehouse_id'], $db) ."</td>";
                echo "<td>". $row['rackno'] ."</td>";
                echo "<td>". $row['in_docketnumber'] ."</td>";
                echo "<td>". $row['remarks'] ."</td>";
                echo "<td>". getName('wms_users', 'username', 'id', $row['userid'], $db) ."</td>";
                echo "<td>". getName('customerinfo', 'cusname', 'cus_id', $row['cus_id'], $db) ."</td>";
                echo "<td>". $row['inserteddate'] ."</td>";
            echo "</tr>";
        }
    echo "<table>";
    echo $pages->page_links();
}
if($_REQUEST['action'] == 'DateBetweenSI') {
    $sess_cusID = $_SESSION['user']['cus_id'];
    $From = trim($_GET['From']);
    $To = trim($_GET['To']);
    
    $pages = new Paginator(' 50', 'page');
    $total = getCountMatchColumn('stockin', 'stockout', 'serialno', 'serialno', 'cus_id', $sess_cusID, $db);
    //$total = getCountMatchColumn('stockin', 'stockin_id', 'cus_id', $sess_cusID, $db);
    $tots = $total[0]['total'];
    $totals = $pages->set_total($tots);
    $limit = $pages->get_limit();
    $rows = dateBetween("stockin", "ASC", "receiveddate", $From, $To, "stockin_id", "cus_id", $sess_cusID, $db);
    //$rows = selectAllMatchColumn("stockin", "stockout", "serialno", "serialno", $limit, "cus_id", $sess_cusID, $db);
    
    echo "<table class=\"row-fluid table table-striped table-bordered\">";
            echo "<tr>";
                echo "<th>". $stock_in['serialno'] ."</th>";
                echo "<th>". $stock_in['project_id'] ."</th>";
                echo "<th>". $stock_in['productid'] ."</th>";
                echo "<th>". $stock_in['description'] ."</th>";
                echo "<th>". $stock_in['revisionstate'] ."</th>";
                echo "<th>". $stock_in['in_quantity'] ."</th>";
                echo "<th>". $stock_in['shiptype'] ."</th>";
                echo "<th>". $stock_in['uom'] ."</th>";
                echo "<th>". $stock_in['invoicenumber'] ."</th>";
                echo "<th>". $stock_in['ship_no'] ."</th>";
                echo "<th>". $stock_in['mawb'] ."</th>";
                echo "<th>". $stock_in['hawb'] ."</th>";
                echo "<th>". $stock_in['receiveddate'] ."</th>";
                echo "<th>". $stock_in['shipfrom'] ."</th>";
                echo "<th>". $stock_in['boxno'] ."</th>";
                echo "<th>". $stock_in['in_rma'] ."</th>";
                echo "<th>". $stock_in['warehouse_name'] ."</th>";
                echo "<th>". $stock_in['rackno'] ."</th>";
                echo "<th>". $stock_in['in_docketnumber'] ."</th>";
                echo "<th>". $stock_in['remarks'] ."</th>";
                echo "<th>". $stock_in['userid'] ."</th>";
                echo "<th>". $stock_in['cus_id'] ."</th>";
                echo "<th>". $stock_in['inserteddate'] ."</th>";
            echo "</tr>";
        foreach ($rows as $row) {
            echo "<tr>";
                echo "<td>". $row['serialno'] ."</td>";
                echo "<td>";
                    echo getName('projects', 'projectname', 'project_id', $row['project_id'], $db);
                    echo "<br/>";
                    $idofcustomer = getName('projects', 'cus_id', 'project_id', $row['project_id'], $db);
                    $customerid = getName('customerinfo', 'cus_id', 'cus_id', $idofcustomer, $db);
                echo "</td>";
                echo "<td>". $row['productid'] ."</td>";
                echo "<td>". $row['description'] ."</td>";
                echo "<td>". $row['revisionstate'] ."</td>";
                echo "<td>". $row['in_quantity'] ."</td>";
                echo "<td>". getName('uom', 'measurement', 'uom_id', $row['uom'], $db) ."</td>";
                echo "<td>". getName('shiptype', 'typename', 'shiptype_id', $row['shiptype'], $db) ."</td>";
                echo "<td>". $row['invoicenumber'] ."</td>";
                echo "<td>". $row['shipid'] ."</td>";
                echo "<td>". $row['mawb'] ."</td>";
                echo "<td>". $row['hawb'] ."</td>";
                echo "<td>". $row['receiveddate'] ."</td>";
                echo "<td>". $row['shipfrom'] ."</td>";
                echo "<td>". $row['boxno'] ."</td>";
                echo "<td>". $row['in_rma'] ."</td>";
                echo "<td>". getName('warehouses', 'warehousename', 'warehouse_id', $row['warehouse_id'], $db) ."</td>";
                echo "<td>". $row['rackno'] ."</td>";
                echo "<td>". $row['in_docketnumber'] ."</td>";
                echo "<td>". $row['remarks'] ."</td>";
                echo "<td>". getName('wms_users', 'username', 'id', $row['userid'], $db) ."</td>";
                echo "<td>". getName('customerinfo', 'cusname', 'cus_id', $row['cus_id'], $db) ."</td>";
                echo "<td>". $row['inserteddate'] ."</td>";
            echo "</tr>";
        }
    echo "<table>";
    echo $pages->page_links();
}

if($_REQUEST['action'] == 'MovementBySerial') {
    $serialno = $_GET['stforSearial'];
    
    $query = "SELECT * FROM stockin WHERE serialno = '".$serialno."'";

    try {
        $stmt = $db->prepare($query);
        $stmt->execute();
    } catch (PDOException $ex) {
        die("Failed to run query: " . $ex->getMessage());
    }
    $rows = $stmt->fetchAll();
    echo "<h5>Stock In Report for ".$serialno."</h5>";
    echo "<table class=\"row-fluid table table-striped table-bordered\">";
            echo "<tr>";
                echo "<th>";
                    echo $stock_in['serialno'];
                echo "</th>";
                echo "<th>";
                    echo $stock_in['project_id'];
                echo "</th>";
                echo "<th>";
                    echo $stock_in['productid'];
                echo "</th>";
                echo "<th>";
                    echo $stock_in['description'];
                echo "</th>";
                echo "<th>";
                    echo $stock_in['revisionstate'];
                echo "</th>";
                echo "<th>";
                    echo $stock_in['uom'];
                echo "</th>";
                echo "<th>";
                    echo $stock_in['in_quantity'];
                echo "</th>";
                echo "<th>";
                    echo $stock_in['shiptype'];
                echo "</th>";
                echo "<th>";
                    echo $stock_in['invoicenumber'];
                echo "</th>";
                echo "<th>";
                    echo $stock_in['ship_no'];
                echo "</th>";
                echo "<th>";
                    echo $stock_in['mawb'];
                echo "</th>";
                echo "<th>";
                    echo $stock_in['hawb'];
                echo "</th>";
                echo "<th>";
                    echo $stock_in['receiveddate'];
                echo "</th>";
                echo "<th>";
                    echo $stock_in['shipfrom'];
                echo "</th>";
                echo "<th>";
                    echo $stock_in['boxno'];
                echo "</th>";
                echo "<th>";
                    echo $stock_in['rma'];
                echo "</th>";
                echo "<th>";
                       echo $stock_in['warehouse_name'];
                echo "</th>";
                echo "<th>";
                    echo $stock_in['rackno'];
                echo "</th>";                
                echo "<th>";
                    echo $stock_in['docketnumber'];
                echo "</th>";
                echo "<th>";
                    echo $stock_in['remarks'];
                echo "</th>";
                echo "<th>";
                    echo $stock_in['userid'];
                echo "</th>";
                echo "<th>";
                    echo $stock_in['cus_id'];
                echo "</th>";
                echo "<th>";
                    echo $stock_in['inserteddate'];
                echo "</th>";
            echo "</tr>";
        foreach ($rows as $row) {
            echo "<tr>";
                echo "<td>";
                    echo $row['serialno'];
                echo "</td>";
                echo "<td>";
                    echo getName('projects', 'projectname', 'project_id', $row['project_id'], $db);
                    echo "<br/>";
                    $idofcustomer = getName('projects', 'cus_id', 'project_id', $row['project_id'], $db);
                    $customerid = getName('customerinfo', 'cus_id', 'cus_id', $idofcustomer, $db);
                echo "</td>";
                echo "<td>";
                    echo $row['productid'];
                echo "</td>";
                echo "<td>";
                    echo $row['description'];
                echo "</td>";
                echo "<td>";
                    echo $row['revisionstate'];
                echo "</td>";
                echo "<td>";
                    echo uomBeauty($row['uom'], $db);
                echo "</td>";
                echo "<td>";
                    echo $row['in_quantity'];
                echo "</td>";
                echo "<td>";
                    echo getName('shiptype', 'typename', 'shiptype_id', $row['shiptype'], $db);
                echo "</td>";
                echo "<td>";
                    echo $row['invoicenumber'];
                echo "</td>";
                echo "<td>";
                    echo $row['shipid'];
                echo "</td>";
                echo "<td>";
                    echo $row['mawb'];
                echo "</td>";
                echo "<td>";
                    echo $row['hawb'];
                echo "</td>";
                echo "<td>";
                    echo $row['receiveddate'];
                echo "</td>";
                echo "<td>";
                    echo $row['shipfrom'];
                echo "</td>";
                echo "<td>";
                    echo $row['boxno'];
                echo "</td>";
                echo "<td>";
                    echo $row['rma'];
                echo "</td>";
                echo "<td>";
                    echo getName('warehouses', 'warehousename', 'warehouse_id', $row['warehouse_id'], $db); 
                echo "</td>";
                echo "<td>";
                    echo $row['rackno'];
                echo "</td>";
                echo "<td>";
                    echo $row['docketnumber'];
                echo "</td>";
                echo "<td>";
                    echo $row['remarks'];
                echo "</td>";
                echo "<td>";
                    echo getName('wms_users', 'username', 'id', $row['userid'], $db);
                echo "</td>";
                echo "<td>";
                    echo getName('customerinfo', 'cusname', 'cus_id', $row['cus_id'], $db);
                echo "</td>";                
                echo "<td>";
                    echo $row['inserteddate'];
                echo "</td>";
            echo "</tr>";
        }
    echo "<table>";
    
    $query1 = "SELECT * FROM stockout WHERE serialno = '".$serialno."'";

    try {
        $stmt = $db->prepare($query1);
        $stmt->execute();
    } catch (PDOException $ex) {
        die("Failed to run query: " . $ex->getMessage());
    }
    $rows1 = $stmt->fetchAll();
    echo "<h5>Stock Out Report for ".$serialno."</h5>";
    echo "<table class=\"row-fluid table table-striped table-bordered\">";
            echo "<tr>";
                echo "<th>";
                    echo $stock_out['serialno'];
                echo "</th>";
                echo "<th>";
                    echo $stock_out['project_id'];
                echo "</th>";
                echo "<th>";
                    echo $stock_out['productid'];
                echo "</th>";
                echo "<th>";
                    echo $stock_out['description'];
                echo "</th>";
                echo "<th>";
                    echo $stock_out['revisionstate'];
                echo "</th>";
                echo "<th>";
                    echo $stock_out['eng_id'];
                echo "</th>";
                echo "<th>";
                    echo $stock_out['responsetype'];
                echo "</th>";
                echo "<th>";
                    echo $stock_out['requesttime'];
                echo "</th>";
                echo "<th>";
                    echo $stock_out['out_docketnumber'];
                echo "</th>";
                echo "<th>";
                    echo $stock_out['out_quantity'];
                echo "</th>";
                echo "<th>";
                    echo $stock_out['uom'];
                echo "</th>";
                echo "<th>";
                    echo $stock_out['requestnumber'];
                echo "</th>";
                echo "<th>";
                    echo $stock_out['requestedby'];
                echo "</th>";
                echo "<th>";
                    echo $stock_out['siteid'];
                echo "</th>";
                echo "<th>";
                    echo $stock_out['boxno'];
                echo "</th>";
                echo "<th>";
                    echo $stock_out['warehouseid'];
                echo "</th>";
                echo "<th>";
                    echo $stock_out['challannumber'];
                echo "</th>";
                echo "<th>";
                    echo $stock_out['receivedtime'];
                echo "</th>";
                echo "<th>";
                    echo $stock_out['deliverytime'];
                echo "</th>";
                echo "<th>";
                    echo $stock_out['sla'];
                echo "</th>";
                echo "<th>";
                    echo $stock_out['receivername'];
                echo "</th>";
                echo "<th>";
                    echo $stock_out['cellnumber'];
                echo "</th>";
                echo "<th>";
                    echo $stock_out['transportername'];
                echo "</th>";
                echo "<th>";
                    echo $employee['employeename'];
                echo "</th>";
                echo "<th>";
                    echo $stock_out['remarks'];
                echo "</th>";
                echo "<th>";
                    echo $stock_out['user_id'];
                echo "</th>";
                echo "<th>";
                    echo $stock_out['inserteddate'];
                echo "</th>";
            echo "</tr>";
            
        foreach ($rows1 as $row) {
            echo "<tr>";
                echo "<td>";
                    echo $row['serialno'];
                echo "</td>";
                echo "<td>";
                    echo getName('projects', 'projectname', 'project_id', $row['project_id'], $db); 
                echo "</td>";
                echo "<td>";
                    echo $row['productid'];
                echo "</td>";
                echo "<td>";
                    echo $row['description'];
                echo "</td>";
                echo "<td>";
                    echo $row['revisionstate'];
                echo "</td>";
                echo "<td>";
                    echo getName('engineerinfo', 'engname', 'eng_id', $row['eng_id'], $db);
                echo "</td>";
                echo "<td>";
                    echo getName('responsetype', 'uniquename', 'responsetype_id', $row['response_type_id'], $db);
                echo "</td>";
                echo "<td>";
                    echo $row['requesttime'];
                echo "</td>";
                echo "<td>";
                    echo $row['out_docketnumber'];
                echo "</td>";
                echo "<td>";
                    echo $row['out_quantity'];
                echo "</td>";
                echo "<td>";
                    echo uomBeauty($row['uom'], $db);
                echo "</td>";
                echo "<td>";
                    echo $row['requestnumber'];
                echo "</td>";
                echo "<td>";
                    echo $row['requestedby'];
                echo "</td>";
                echo "<td>";
                    echo $row['siteid'];
                echo "</td>";
                echo "<td>";
                    echo $row['boxno'];
                echo "</td>";
                echo "<td>";
                   echo getName('warehouses', 'warehousename', 'warehouse_id', $row['warehouseid'], $db); 
                echo "</td>";
                echo "<td>";
                    echo $row['challannumber'];
                echo "</td>";
                echo "<td>";
                    echo $row['receivedtime'];
                echo "</td>";
                echo "<td>";
                    echo $row['deliverytime'];
                echo "</td>";
                echo "<td>";
                    //echo $row['sla'];
                    $pickDiff = dateDiff($row['deliverytime'], $row['requesttime']);
                    
                    $pickSla = getName('responsetype', 'hour', 'responsetype_id', $row['response_type_id'], $db);
                    
                     if($row['checkSla'] == 1) {
                        echo "<img src='".CONTENTPATH."/foo-drawer/img/select.png' width='16' />";
                    } else {
                        echo "<img src='".CONTENTPATH."/foo-drawer/img/cross.png' width='16' />";
                    }
                echo "</td>";
                echo "<td>";
                    echo $row['receivername'];
                echo "</td>";
                echo "<td>";
                    echo $row['cellnumber'];
                echo "</td>";
                echo "<td>";
                    echo $row['transportername'];
                echo "</td>";
                echo "<td>";
                    echo getName('employee', 'employeename', 'employee_id', $row['employee_id'], $db); 
                echo "</td>";
                echo "<td>";
                    echo $row['remarks'];
                echo "</td>";
                echo "<td>";
                    echo getName('wms_users', 'username', 'id', $row['userid'], $db);
                echo "</td>";
                echo "<td>";
                    echo $row['inserteddate'];
                echo "</td>";
            echo "</tr>";
        }
    echo "<table>";
    
    $query2 = "SELECT * FROM faulties WHERE serialno = '".$serialno."'";

    try {
        $stmt = $db->prepare($query2);
        $stmt->execute();
    } catch (PDOException $ex) {
        die("Failed to run query: " . $ex->getMessage());
    }
    $rows2 = $stmt->fetchAll();
    echo "<h5>Faulties Report for ".$serialno."</h5>";
    
    echo "<table class=\"row-fluid table table-striped table-bordered\">";
            echo "<tr>";
                echo "<th>";
                    echo $faulties['serialno'];
                echo "</th>";
                echo "<th>";
                    echo $faulties['project_id'];
                echo "</th>";
                echo "<th>";
                    echo $faulties['rma'];
                echo "</th>";
                echo "<th>";
                    echo $faulties['pickuprequestdate'];
                echo "</th>";
                echo "<th>";
                    echo $faulties['physicallyreceivedate'];
                echo "</th>";
                echo "<th>";
                    echo $faulties['productid'];
                echo "</th>";
                echo "<th>";
                    echo $faulties['description'];
                echo "</th>";
                echo "<th>";
                    echo $faulties['revisionstate'];
                echo "</th>";
                echo "<th>";
                    echo $faulties['boxno'];
                echo "</th>";
                echo "<th>";
                    echo $faulties['return_quantity'];
                echo "</th>";
                echo "<th>";
                    echo $faulties['uom'];
                echo "</th>";
                echo "<th>";
                    echo $faulties['manufacturingdate'];
                echo "</th>";
                echo "<th>";
                    echo $faulties['faultiesstatus'];
                echo "</th>";
                echo "<th>";
                    echo $faulties['receivedfrom'];
                echo "</th>";
                echo "<th>";
                    echo $faulties['siteid'];
                echo "</th>";
                echo "<th>";
                    echo $faulties['warehouseid'];
                echo "</th>";
                echo "<th>";
                    echo $employee['employee_id'];
                echo "</th>";
                echo "<th>";
                    echo $faulties['docketdate'];
                echo "</th>";
                echo "<th>";
                    echo $faulties['faulties_docketnumber'];
                echo "</th>";
                echo "<th>";
                    echo $faulties['remarks'];
                echo "</th>";
                echo "<th>";
                    echo $faulties['userid'];
                echo "</th>";
                echo "<th>";
                    echo $faulties['inserteddate'];
                echo "</th>";
            echo "</tr>";
            
        foreach ($rows2 as $row) {
            echo "<tr>";
                echo "<td>";
                    echo $row['serialno'];
                echo "</td>";
                echo "<td>";
                    echo getName('projects', 'projectname', 'project_id', $row['project_id'], $db);
                echo "</td>";
                echo "<td>";
                    echo $row['rma'];
                echo "</td>";
                echo "<td>";
                    echo $row['pickuprequestdate'];
                echo "</td>";
                echo "<td>";
                    echo $row['physicallyreceivedate'];
                echo "</td>";
                echo "<td>";
                    echo $row['productid'];
                echo "</td>";
                echo "<td>";
                    echo $row['description'];
                echo "</td>";
                echo "<td>";
                    echo $row['revisionstate'];
                echo "</td>";
                echo "<td>";
                    echo $row['boxno'];
                echo "</td>";
                echo "<td>";
                    echo $row['return_quantity'];
                echo "</td>";
                echo "<td>";
                    echo uomBeauty($row['uom'], $db);
                echo "</td>";
                echo "<td>";
                    echo $row['manufacturingdate'];
                echo "</td>";
                echo "<td>";
                    echo getName('faultystatus', 'statusname', 'status_id', $row['faultiesstatus'], $db);
                echo "</td>";
                echo "<td>";
                    echo $row['receivedfrom'];
                echo "</td>";
                echo "<td>";
                    echo $row['siteid'];
                echo "</td>";
                echo "<td>";
                    echo getName('warehouses', 'warehousename', 'warehouse_id', $row['warehouseid'], $db);
                echo "</td>";
                echo "<td>";
                    echo getName('employee', 'employeename', 'employee_id', $row['employeeid'], $db);
                echo "</td>";
                echo "<td>";
                    echo $row['docketdate'];
                echo "</td>";
                echo "<td>";
                    echo $row['return_docketnumber'];
                echo "</td>";
                echo "<td>";
                    echo $row['remarks'];
                echo "</td>";
                echo "<td>";
                    echo getName('wms_users', 'username', 'id', $row['userid'], $db);
                echo "</td>";
                echo "<td>";
                    echo $row['inserteddate'];
                echo "</td>";
            echo "</tr>";
        }
    echo "<table>";
    
    
    $query3 = "SELECT * FROM faulty_export WHERE serialno = '".$serialno."'";

    try {
        $stmt = $db->prepare($query2);
        $stmt->execute();
    } catch (PDOException $ex) {
        die("Failed to run query: " . $ex->getMessage());
    }
    $rows3 = $stmt->fetchAll();
    echo "<h5>Faulty Export Report for ".$serialno."</h5>";
    
    echo "<table class=\"row-fluid table table-striped table-bordered\">";
            echo "<tr>";
                echo "<th>";
                    echo $faulty_export['serialno'];
                echo "</th>";
                echo "<th>";
                    echo $faulty_export['project_id'];
                echo "</th>";
                echo "<th>";
                    echo $faulty_export['productid'];
                echo "</th>";
                echo "<th>";
                    echo $faulty_export['description'];
                echo "</th>";
                echo "<th>";
                    echo $faulty_export['revisionstate'];
                echo "</th>";
                echo "<th>";
                    echo $faulty_export['boxno'];
                echo "</th>";
                echo "<th>";
                    echo $faulty_export['faulty_export_quantity'];
                echo "</th>";
                echo "<th>";
                    echo $faulty_export['uom'];
                echo "</th>";
                echo "<th>";
                    echo $faulty_export['handedoverto'];
                echo "</th>";
                echo "<th>";
                    echo $faulty_export['manufacturingdate'];
                echo "</th>";
                echo "<th>";
                    echo $faulty_export['faultydocketnumber'];
                echo "</th>";
                echo "<th>";
                    echo $faulty_export['docketdate'];
                echo "</th>";
                echo "<th>";
                    echo $faulty_export['siteid'];
                echo "</th>";
                echo "<th>";
                    echo $faulty_export['warehouse_id'];
                echo "</th>";
                echo "<th>";
                    echo $faulty_export['receivedfrom'];
                echo "</th>";
                echo "<th>";
                    echo $faulty_export['rr_rma'];
                echo "</th>";
                echo "<th>";
                    echo $faulty_export['export_rma'];
                echo "</th>";
                echo "<th>";
                    echo $faulty_export['rr_invoiceno'];
                echo "</th>";
                echo "<th>";
                    echo $faulty_export['rr_mawb'];
                echo "</th>";
                echo "<th>";
                    echo $faulty_export['rr_awb'];
                echo "</th>";
                echo "<th>";
                    echo $faulty_export['rr_date'];
                echo "</th>";
                echo "<th>";
                    echo $faulty_export['remarks'];
                echo "</th>";
                echo "<th>";
                    echo $faulty_export['userid'];
                echo "</th>";
                echo "<th>";
                    echo $faulty_export['inserteddate'];
                echo "</th>";
            echo "</tr>";
            
        foreach ($rows3 as $row) {
            echo "<tr>";
                echo "<td>";
                    echo $row['serialno'];
                echo "</td>";
                echo "<td>";
                    echo getName('projects', 'projectname', 'project_id', $row['project_id'], $db);
                echo "</td>";
                echo "<td>";
                    echo $row['description'];
                echo "</td>";
                echo "<td>";
                    echo $row['productid'];
                echo "</td>";
                echo "<td>";
                    echo $row['revisionstate'];
                echo "</td>";
                echo "<td>";
                    echo $row['boxno'];
                echo "</td>";
                echo "<td>";
                    echo $row['faulty_export_quantity'];
                echo "</td>";
                echo "<td>";
                    echo uomBeauty($row['uom'], $db);
                echo "</td>";
                echo "<td>";
                    echo $row['handedoverto'];
                echo "</td>";
                echo "<td>";
                    echo $row['manufacturingdate'];
                echo "</td>";
                echo "<td>";
                    echo $row['faultydocketnumber'];
                echo "</td>";
                echo "<td>";
                    echo $row['docketdate'];
                echo "</td>";
                echo "<td>";
                    echo $row['siteid'];
                echo "</td>";
                echo "<td>";
                    echo getName('warehouses', 'warehousename', 'warehouse_id', $row['warehouseid'], $db);
                echo "</td>";
                echo "<td>";
                    echo $row['receivedfrom'];
                echo "</td>";
                echo "<td>";
                    echo $row['rr_rma'];
                echo "</td>";
                echo "<td>";
                    echo $row['export_rma'];
                echo "</td>";
                echo "<td>";
                    echo $row['rr_invoiceno'];
                echo "</td>";
                echo "<td>";
                    echo $row['rr_mawb'];
                echo "</td>";
                echo "<td>";
                    echo $row['rr_awb'];
                echo "</td>";
                echo "<td>";
                    echo $row['rr_date'];
                echo "</td>";
                echo "<td>";
                    echo $row['remarks'];
                echo "</td>";
                echo "<td>";
                    echo getName('wms_users', 'username', 'id', $row['userid'], $db);
                echo "</td>";
                echo "<td>";
                    echo $row['inserteddate'];
                echo "</td>";
            echo "</tr>";
        }
    echo "<table>";
}
